package com.kehutong.payfees.admin;

import java.io.File;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;

import com.kehutong.common.util.Token;
import com.kehutong.payfees.enums.FeesType;
import com.kehutong.payfees.util.BasicClient;
import com.kehutong.payfees.util.ExcelUtil;
import com.kehutong.payfees.util.JDBCUtil;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.coraframework.authz.HasPermission;
import org.coraframework.inject.Inject;
import org.coraframework.json.JSONArray;
import org.coraframework.json.JSONObject;
import org.coraframework.mvc.MvcAction;
import org.coraframework.mvc.http.Bean;
import org.coraframework.mvc.http.ReqMapping;
import org.coraframework.mvc.http.result.Result;
import org.coraframework.orm.Pool;
import org.coraframework.orm.jdbc.JdbcSession;
import org.coraframework.orm.jdbc.execute.entity.FindPage;
import org.coraframework.util.Objects;

import com.kehutong.common.ExcelHeaders;
import com.kehutong.payfees.entity.ChargeFeeType;
import com.kehutong.payfees.entity.ChargeTemplate;

/**
 * 缴费模板
 */
@MvcAction
@ReqMapping("/admin/charge/template")
public class ChargeTemplateAction {

    @Inject
    private JdbcSession jdbcSession;
    @Inject
    private BasicClient basicClient;


    @HasPermission("charge:template:view")
    @ReqMapping("/page")
    public Object page(JSONObject req) throws Exception {
        FindPage<ChargeTemplate> find = jdbcSession.findPage(ChargeTemplate.class)
        			.eq(req,  "updateTime")
                    .like(req, "items", "name", "updateBy")
                    .eq("deleted", false)
                    .order("createTime", "DESC")
                    .order("id", "DESC")
                    .page(req);

        JDBCUtil.between(req, find, "updateTime");

        return find.exe();
    }

    /**
     * 下拉列表获取
     */
    @HasPermission("charge:template:view")
    @ReqMapping("/list")
    public Object list(JSONObject req) {
        List<ChargeTemplate> template = jdbcSession.findArray(ChargeTemplate.class)
                .eq("deleted", false)
                .order("createTime", "DESC")
                .exe();

        return Result.success(template);
    }


    @Bean(newInstance=true, copy=true)
    @HasPermission("charge:template:edit")
    @ReqMapping("/save")
    public Object save(Token token, ChargeTemplate item, JSONObject jsonObject) {
        if (Objects.isEmpty(item.getName())) {
            return Result.error(0, "模板名称不能为空");
        }

        JSONArray items = jsonObject.getJSONArray("items");
        if (Objects.isEmpty(items)) {
            return Result.error(0, "收费科目不能为空");
        }

        if (existSameName(item)) {
            return Result.error(0, item.getName() + "已存在，请勿重复添加");
        }

        // 科目去重
        item.setItems(items.stream()
                .distinct()
                .map(Object::toString)
                .collect(Collectors.toList()));
        // 创建人信息
        item.setCreateBy(token.getUsername());
        item.setUpdateBy(token.getUsername());

        jdbcSession.insert(item);

        return Result.success();
    }

    @HasPermission("charge:template:view")
    @ReqMapping("/check")
    public Object check(String id) throws Exception {
        ChargeTemplate item = Pool.get(ChargeTemplate.class, id);

        List<ChargeFeeType> feeTypes = jdbcSession.findArray(ChargeFeeType.class)
                .in("id", item.getItems())
                .exe();

        for (ChargeFeeType feeType: feeTypes) {
            if(Objects.isNull(feeType.getFeetype())){
                return Result.error(0, "所选模板包含未定义收费类型的费用科目，请先到收费科目完成定义");
            }
        }

        return Result.success();
    }

    @HasPermission("charge:template:edit")
    @ReqMapping("/delete")
    public Object delete(ChargeTemplate item) throws Exception {
    	if (Objects.nonNull(item)) {
    		item.setDeleted(true);
    		jdbcSession.updateById(item);
    	}
        
        return Result.success();
    }

    @Bean(copy=true)
    @HasPermission("charge:template:edit")
    @ReqMapping("/update")
    public Object update(Token token, ChargeTemplate item, JSONObject jsonObject) {
        if (Objects.isEmpty(item.getName())) {
            return Result.error(0, "模板名称不能为空");
        }

        JSONArray items = jsonObject.getJSONArray("items");
        if (Objects.isEmpty(items)) {
            return Result.error(0, "收费科目不能为空");
        }


        if (existSameName(item)) {
            return Result.error(0, item.getName() + "已存在，请勿重复添加");
        }

        // 去重
        item.setItems(items.stream()
                .distinct()
                .map(Object::toString)
                .collect(Collectors.toList()));

        // 更新人信息
        item.setUpdateBy(token.getUsername());

        jdbcSession.updateById(item);

        return Result.success();
    }

    @ReqMapping("/get")
    public Object get(ChargeTemplate item) {
        return Result.success(item);
    }

    @HasPermission("charge:template:view")
    @ReqMapping("/download")
    public Object download(Token token, JSONObject req) throws Exception {

        ChargeTemplate template = jdbcSession.findOne(ChargeTemplate.class)
                .eq("id", req.getString("templateId"))
                .exe();
        if (template == null) {
            return Result.error(0, "不存在的模板");
        }
        if (Objects.isEmpty(template.getItems())) {
            return Result.error(0, "该模板未添加收费科目");
        }

        if (Objects.isEmpty(req.getString("communityNo"))) {
            return Result.error(0, "未选择小区");
        }

        if(Objects.isEmpty(req.getString("billDateFrom")) || Objects.isEmpty(req.getString("billDateTo"))){
            return Result.error(0, "账单周期起止日期不能为空");
        }

        if(Objects.isEmpty(req.getString("shouldPayDate"))){
            return Result.error(0, "应付款日期不能为空");
        }

        Map<String, JSONObject> communityMap = basicClient.getCommunityMap(token);

        JSONArray houses = basicClient.getHouses(token, req);

        if(Objects.isNull(houses)){
            return null;
        }

        Workbook wb = generateTemplateExportWorkBook(token, req, template, houses, communityMap);

        File tmpFile = ExcelUtil.writeXlsFile(wb, "缴费任务导入模板", ".xls");
        return Result.download(tmpFile);
    }

    private Workbook generateTemplateExportWorkBook(Token token, JSONObject req, ChargeTemplate template, JSONArray houses, Map<String, JSONObject> communityMap) throws Exception {
        // 生成模板文件
        HSSFWorkbook wb = new HSSFWorkbook();
        //创建工具
        CreationHelper helper = wb.getCreationHelper();
        //单元格样式类
        CellStyle dateCellStyle = wb.createCellStyle();
        //设置单元格格式
        dateCellStyle.setDataFormat(helper.createDataFormat().getFormat("yyyy-MM-dd"));

        HSSFCellStyle yellowStyle = wb.createCellStyle();
        yellowStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        yellowStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        HSSFCellStyle chocolateStyle = wb.createCellStyle();
        chocolateStyle.setFillForegroundColor(IndexedColors.ROSE.getIndex());
        chocolateStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        HSSFCellStyle blueStyle = wb.createCellStyle();
        blueStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        blueStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        Sheet sheet = wb.createSheet();
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5));
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 6, 8));


        List<String> templateItems = template.getItems();
        List<ChargeFeeType> feeTypes = jdbcSession.findArray(ChargeFeeType.class)
                .in("id", templateItems)
                .exe();
        Map<String, ChargeFeeType> feeTypeMap = feeTypes.stream().collect(Collectors.toMap(ChargeFeeType::getId, Function.identity()));

        if (templateItems.size() > 0) {
            int size = 0;
            for (String item : templateItems) {
                ChargeFeeType feeType = feeTypeMap.get(item);
                if(FeesType.INSTRUMENT == feeType.getFeetype()) {
                    size+=7;
                }else{
                    size+=3;
                }
            }
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 9, 9 + size - 1));
        }

        // 首行 带颜色的说明文字
        Row firstRow = sheet.createRow(0);
        Cell firstRowCell = firstRow.createCell(0);
        firstRowCell.setCellValue("由房屋信息表导入后导出（只读字段）");
        firstRowCell.setCellStyle(yellowStyle);

        firstRowCell = firstRow.createCell(6);
        firstRowCell.setCellValue("固定录入字段，按照格式录入");
        firstRowCell.setCellStyle(chocolateStyle);

        firstRowCell = firstRow.createCell(9);
        firstRowCell.setCellValue("科目由模板制定，需录入具体金额");
        firstRowCell.setCellStyle(blueStyle);

        Row headerRow = sheet.createRow(1);
        int headerCellNum = 0;
        for (String s : ExcelHeaders.CHARGE_IMPORT_BILL_EXCEL_HEADER) {
            Cell cell = headerRow.createCell(headerCellNum++);
            cell.setCellValue(s);
        }

        for (int i = 0, len = templateItems.size(); i < len; i ++) {
            ChargeFeeType feeType = feeTypeMap.get(templateItems.get(i));
            if(FeesType.INSTRUMENT == feeType.getFeetype()) {
                for (String s : ExcelHeaders.CHARGE_IMPORT_BILL_ITEM_INSTRUMENT_EXECEL_HEADER) {
                    Cell cell = headerRow.createCell(headerCellNum++);
                    cell.setCellValue(s);
                }
            }else{
                for (String s : ExcelHeaders.CHARGE_IMPORT_BILL_ITEM_CONVENTIONAL_EXECEL_HEADER) {
                    Cell cell = headerRow.createCell(headerCellNum++);
                    cell.setCellValue(s);
                }
            }
        }

        int cellNum = 0;

        for (int i = 0; i < houses.size(); i++) {
            JSONObject h = (JSONObject) houses.get(i);
            Row row = sheet.createRow(i + 2);
            cellNum = 0;
            String houseId = h.getString("id");
            row.createCell(cellNum++).setCellValue(houseId);
            row.createCell(cellNum++).setCellValue(communityMap.get(h.getString("communityNo")).getString("name"));
            row.createCell(cellNum++).setCellValue(h.getString("buildName"));
            row.createCell(cellNum++).setCellValue(h.getString("unitName"));
            row.createCell(cellNum++).setCellValue(h.getString("floorName"));
            row.createCell(cellNum++).setCellValue(h.getString("name"));

            Cell tmpCell = row.createCell(cellNum++);
            tmpCell.setCellStyle(dateCellStyle);
            tmpCell.setCellValue(req.getDate("billDateFrom"));

            tmpCell = row.createCell(cellNum++);
            tmpCell.setCellStyle(dateCellStyle);
            tmpCell.setCellValue(req.getDate("billDateTo"));

            tmpCell = row.createCell(cellNum++);
            tmpCell.setCellStyle(dateCellStyle);
            tmpCell.setCellValue(req.getDate("shouldPayDate"));

            for (String item : templateItems) {
                ChargeFeeType feeType = feeTypeMap.get(item);

                tmpCell = row.createCell(cellNum++);
                tmpCell.setCellStyle(dateCellStyle);
                tmpCell.setCellValue(item);

                tmpCell = row.createCell(cellNum++);
                tmpCell.setCellStyle(dateCellStyle);
                tmpCell.setCellValue(feeType.getName());

                if(FeesType.INSTRUMENT == feeType.getFeetype()) {
                    tmpCell = row.createCell(cellNum++);
                    tmpCell.setCellValue("0");

                    tmpCell = row.createCell(cellNum++);
                    tmpCell.setCellValue("0");

                    tmpCell = row.createCell(cellNum++);
                    tmpCell.setCellValue("0");

                    tmpCell = row.createCell(cellNum++);
                    tmpCell.setCellValue("0");
                }

                tmpCell = row.createCell(cellNum++);
                tmpCell.setCellValue("0");
            }
        }


        // 自动列宽
        for (int i = 0; i < cellNum; i++) {
            if (i == 0) {
                // id列不调整
                continue;
            }
            // 自动列宽
            ExcelUtil.autoSizeColumn(sheet, i);
        }
        return wb;
    }

    /**
     * 是否存在重名
     */
    private boolean existSameName(ChargeTemplate template) {
        return !jdbcSession.findArray(ChargeTemplate.class)
                .eq("name", template.getName())
                // 传入的entity已携带id
                .noEq("id", template.getId())
                .eq("deleted", false)
                .exe().isEmpty();
    }
}
